<?php

namespace App\Http\Controllers;

use Illuminate\Foundation\Bus\DispatchesJobs;
use Illuminate\Routing\Controller as BaseController;
use Illuminate\Foundation\Validation\ValidatesRequests;
use Illuminate\Foundation\Auth\Access\AuthorizesRequests;
use DB;

class Controller extends BaseController
{
    use AuthorizesRequests, DispatchesJobs, ValidatesRequests;

    /**
     * @param $table String 查询的表
     * @param $field String 查询的字段
     * @param $time String 查询的时间范围
     * @param $sum String 需要统计的和
     * 作者:周雨婷
     * 时间:2018-06-12
     * 描述：统计总数或统计总和
     */
    public function statistics($table,$field = '',$time = '',$sum = ''){
        if($time == 'count'){
            //总数
            $map = '';
        }elseif($time == 'today'){
            //今日
            $map['start_time'] = date('Y-m-d 00:00:00');
            $map['end_time'] = date('Y-m-d H:i:s');
        }elseif($time == 'yesterday'){
            //昨日
            $map['start_time'] = date('Y-m-d 00:00:00',strtotime("-1 days"));
            $map['end_time'] = date('Y-m-d 00:00:00');
        }elseif($time == 'week'){
            //本周
            $map['start_time'] = date('Y-m-d 00:00:00',mktime(0, 0 , 0,date("m"),date("d")-date("w")+1,date("Y")));
            $map['end_time'] = date('Y-m-d H:i:s');
        }elseif($time == 'lastweek'){
            //上周
            $map['start_time'] = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1-7,date("Y")));
            $map['end_time'] = date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("d")-date("w")+7-7,date("Y")));
        }elseif($time == 'month'){
            //本月
            $map['start_time'] = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
            $map['end_time'] = date('Y-m-d H:i:s');
        }elseif($time == 'lastmonth'){
            //上月
            $map['start_time'] = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m")-1,1,date("Y")));
            $map['end_time'] = date("Y-m-d H:i:s",mktime(23,59,59,date("m") ,0,date("Y")));
        }elseif($time == 'quarter'){
            //本季度
            $season = ceil((date('n'))/3);//当月是第几季度
            $map['start_time'] = date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y')));
            $map['end_time'] = date('Y-m-d H:i:s');
        }elseif($time == 'lastquarter'){
            //上季度
            $season = ceil((date('n'))/3)-1;//上季度是第几季度
            $map['start_time'] = date('Y-m-d H:i:s', mktime(0, 0, 0,$season*3-3+1,1,date('Y')));
            $map['end_time'] = date('Y-m-d H:i:s', mktime(23,59,59,$season*3,date('t',mktime(0, 0 , 0,$season*3,1,date("Y"))),date('Y')));
        }

        if($map == '' && $sum == ''){
            return DB::table($table) -> count();
        }elseif($sum == ''){
            return DB::table($table) -> whereBetween($field,[$map['start_time'],$map['end_time']]) -> count();
        }else{
            return DB::table($table) -> whereBetween($field,[$map['start_time'],$map['end_time']]) -> sum($sum);
        }

    }

    public function getLastSql(){
        DB::connection()->enableQueryLog(); // 开启查询日志
        DB::table('xxx');//查询
        $queries = DB::getQueryLog(); // 获取查询日志
    }
}
